package com.eascs.admin.o2o.o2ozx.dao;

import java.util.GregorianCalendar;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.common.utils.StringUtils;
import com.eascs.admin.o2o.internal.util.BaseUtils;
import com.eascs.admin.o2o.zx.entity.O2oLoanUserEntityZx;
import com.eascs.admin.o2o.zx.entity.O2oZxEntity;
import com.eascs.admin.o2o.zx.vo.O2oZxVo;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.DateUtils;
import com.eascs.common.util.StringUtil;
import com.eascs.web.sso.UserHolder;

@Repository
public class O2oZxDao extends BaseO2oDao {
	private final Logger logger = LoggerFactory.getLogger(O2oZxDao.class);
	public O2oZxEntity getEntityByBillCode(String billcode) {
		String sql = "select * from o2o_zx ols where ols.is_del = 'N' and ols.bill_code = ? ";
		return this.findEntity(sql, O2oZxEntity.class, billcode);
	}
	
	public O2oLoanUserEntityZx getLoanUserZx(String zxno) {
		String sql = "SELECT * from o2o_loan_user_zx u where u.zx_no = ?";
		return this.findEntity(sql, O2oLoanUserEntityZx.class, zxno);
	}
	
	
	/**
	 * @Title: queryFqZxbg
	 * @Description: 根据贷款编号查询夫妻双方的征信报告
	 * @author 张庆奇
	 * @param loanapplyNo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2017年9月6日 下午1:28:18
	 */
	public PageResult<Object[]> queryFqZxbg(String loanapplyNo){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append("select zx.id,u.document_no,zx.bill_code,u.user_full_name ,zx.zx_bank,uzx.zx_date,uzx.zx_td_date,u.relation_date,u.relation_name,zx.zx_status from  o2o_loan_user u left join ");  
		sql.append("o2o_zx zx ON  u.zx_no=zx.id and zx.is_del='N' left join o2o_loan_user_zx  uzx on uzx.zx_no=zx.id  and u.loan_id=uzx.loan_id ");
		sql.append(" where u.loanapply_no=? and sponsor_type is null ");
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	/**
	 * @Title: loanFqZxbg
	 * @Description: 根据身份证查询借款人或者夫妻的征信报告
	 * @author 张庆奇
	 * @param loanapplyNo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2017年9月6日 下午1:28:18
	 */
	public PageResult<Object[]> loanFqZxbg(String IdNum){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append("select uzx.id,zx.bill_code,uzx.user_full_name ,uzx.document_no,uzx.zx_date,uzx.zx_td_date,zx.create_date,zx.zx_status from  ");  
		sql.append(" o2o_zx zx   left join o2o_loan_user_zx uzx on uzx.zx_no=zx.id and  zx.is_del='N' ");
		sql.append(" where uzx.document_no=?  ");
		//sql.append(" and zx.zx_status='CLO' and uzx.zx_date is not null and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(uzx.zx_date)  ");
		sql.append("  order by uzx.zx_date desc ");
		sqlPageQuery.addParam(IdNum);
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	/**
	 * @Title: loanFqZxbg
	 * @Description: 根据身份证及内部审批银行查询借款人或者夫妻的征信报告
	 * @author CXW
	 * @param loanapplyNo
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2018年3月7日 下午10:28:18
	 */
	public PageResult<Object[]> loanFqZxbgNew(String IdNum, String bankLoanName){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append("select zx.id,zx.bill_code,uzx.user_full_name ,uzx.document_no,uzx.zx_date, ");  
		sql.append(" uzx.zx_td_date,zx.create_date,zx.zx_status from  ");
		sql.append(" o2o_zx zx left join o2o_loan_user_zx uzx on uzx.zx_no=zx.id and  zx.is_del='N' ");
		sql.append(" where uzx.document_no in (?) and zx.zx_bank=? ");
		sql.append(" and zx.id not in (SELECT ozs.zx_no FROM o2o_zx_supplement ozs where ozs.is_delete='N') ");
		sql.append("  order by uzx.zx_date desc ");
		sqlPageQuery.addParam(IdNum);
		sqlPageQuery.addParam(bankLoanName);
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
	/**
	 * 征信分页查询
	 * @param O2oZxVo
	 * @return
	 */
	public PageResult<Object[]> findByPage(O2oZxVo o2oZxVo) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,o2oZxVo.getPageSize() ,
				o2oZxVo.getPageIndex() == null ? BaseUtils.PAGE_INDEX : o2oZxVo.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" select zx.id,zx.bill_code ,ouz.user_full_name,zx.zx_type,zx.zx_bank, ");
		sql.append(" zx.operator_name,zx.ltd_name,zx.create_date,zx_status,(case zx.is_del when  'N' then '正常' else '作废' end) as is_del from  o2o_zx zx left join ");
		sql.append(" o2o_loan_user_zx ouz on ouz.zx_no=zx.id  where  ");
		sql.append(" zx.is_del !='Y'  "); //(作废，和正常的都显示)
		sql.append(" and (zx.create_id="+UserHolder.getUser().getNo());
		if (!StringUtils.isEmpty(o2oZxVo.getType())) {
			sql.append(" or zx.operator_id in ("+o2oZxVo.getType()+"))");
		}else {
			sql.append(" or /**DATA_PERMISSION(zx.LTD)**/ 1=1 ) ");
		}
		
		if (StringUtil.hasText(o2oZxVo.getBillCode())) {
			sql.append(" and zx.bill_code like ?");
			pageQuery.addLikeParam(o2oZxVo.getBillCode());
		}
		
		if (StringUtil.hasText(o2oZxVo.getOperatorName())) {
			sql.append(" and zx.operator_name like ?");
			pageQuery.addLikeParam(o2oZxVo.getOperatorName());
		}
		if (StringUtil.hasText(o2oZxVo.getUserFullName())) {
			sql.append(" and ouz.user_full_name like ?");
			pageQuery.addLikeParam(o2oZxVo.getUserFullName());
		}
		if (StringUtil.hasText(o2oZxVo.getZxBank())) {
			sql.append(" and zx.zx_bank = ?");
			pageQuery.addParam(o2oZxVo.getZxBank());
		}
		if (StringUtil.hasText(o2oZxVo.getZxStatus())) {
			sql.append(" and zx.zx_status = ?");
			pageQuery.addParam(o2oZxVo.getZxStatus());
		}
		if (StringUtil.hasText(o2oZxVo.getLtdName())) {
			sql.append(" and zx.ltd_name like ?");
			pageQuery.addLikeParam(o2oZxVo.getLtdName());
		}
		if (StringUtil.hasText(o2oZxVo.getZxType())) {
			sql.append(" and zx.zx_type like ?");
			pageQuery.addLikeParam(o2oZxVo.getZxType());
		}
		
		if (StringUtil.hasText(o2oZxVo.getCreateStartTime())) {
			sql.append(" and zx.create_date >= ? ");
			pageQuery.addParam(o2oZxVo.getCreateStartTime());
		}
		if (StringUtil.hasText(o2oZxVo.getCreateEndTime())) {
			sql.append(" and zx.create_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(o2oZxVo.getCreateEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		if (StringUtil.hasText(o2oZxVo.getZxStartTime())) {
			sql.append(" and ouz.zx_date >= ? ");
			pageQuery.addParam(o2oZxVo.getZxStartTime());
		}
		if (StringUtil.hasText(o2oZxVo.getZxEndTime())) {
			sql.append(" and ouz.zx_date <= ? ");
			GregorianCalendar gc = new GregorianCalendar();
			gc.setTime(DateUtils.formateStandDate(o2oZxVo.getZxEndTime()));
			gc.add(5, 1);
			pageQuery.addParam(gc.getTime());
		}
		
		sql.append(" order by zx.create_date	desc ");
		//pageQuery.setSumColumnIndexs(new int[]{10});
		pageQuery.setQuerySql(sql.toString());
		logger.info("findByPage,sql={}", sql.toString());
		return this.findDataWithPage(pageQuery);
	}

	/**@Description:
	 *               <p>
	 *               夫妻双方历史征信报告
	 *               </p>
	 * @author LMX
	 * @date 2018年1月23日 下午3:44:37
	 * @version V1.0
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public PageResult<Object[]> queryHistoryfqZx(String loanapplyNo, String loanId) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dl.datalist_no,zx.zx_type,dl.original_name,dl.creat_or,dl.creat_date,dl.update_time ");
		sql.append(" FROM o2o_datalist dl  ");
		sql.append(" LEFT JOIN o2o_loan_user u ON u.zx_no=dl.loanapply_no  ");
		sql.append(" LEFT JOIN o2o_zx zx ON u.zx_no=zx.id AND zx.is_del='N'  ");
		sql.append(" WHERE 1=1 AND dl.is_delete='N' AND dl.datalist_code='ZXBGDATA' ");//#//个人征信报告dl.datalist_code='ZXBGDATA'
		sql.append(" AND u.loanapply_no=( ");
		sql.append("    SELECT A.loanapply_no FROM (  ");//#上一笔贷款单的单号
		sql.append(" 		 SELECT l.payment_time,l.loanapply_no ");
		sql.append("         FROM o2o_loanapply l  ");
		sql.append(" 		 WHERE 1=1 AND l.loan_flg='Y' AND l.wf_type IN('O','HN','DC','YQ')   ");
		sql.append(" 		 AND l.payment_time <(SELECT CASE WHEN a.payment_time IS NULL THEN now() ELSE a.payment_time END FROM o2o_loanapply a WHERE a.loanapply_no=? )  ");
		sql.append(" 		 AND l.loan_id=? ");
		sql.append(" 		 ORDER BY l.payment_time DESC  ");
		sql.append(" 		 LIMIT 0,1   ");//#LIMIT 19,10表示从第20条开始取10条，也就是20～30或者LIMIT 1查第一条数据
		sql.append("    ) AS A ");
		sql.append(" ) ");
//		sqlPageQuery.addParam(FileTypeConstants.ENTERPRISE_ZX_IDCARD);//个人征信报告
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.addParam(loanId);
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	/**@Description:
	 *               <p>
	 *               企业历史征信报告
	 *               </p>
	 * @author LMX
	 * @date 2018年1月24日 上午10:00:11
	 * @version V1.0
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public PageResult<Object[]> queryHistoryQyZx(String loanapplyNo, String loanId) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT dl.datalist_no,dl.original_name,dl.creat_or,dl.creat_date,dl.update_time ");
		sql.append(" FROM o2o_datalist dl   ");
		sql.append(" LEFT JOIN o2o_enterprise_zx ezx ON ezx.id=dl.loanapply_no  ");
		sql.append(" WHERE 1=1 AND dl.is_delete='N' AND dl.datalist_code='ENTERPRISEZXIDCARD'  ");//#企业征信报告dl.datalist_code='ENTERPRISEZXIDCARD'
		sql.append(" AND ezx.loanapply_no=( ");
		sql.append("    SELECT A.loanapply_no FROM (  ");//#上一笔贷款单的单号
		sql.append(" 		 SELECT l.payment_time,l.loanapply_no  ");
		sql.append("         FROM o2o_loanapply l   ");
		sql.append(" 		 WHERE 1=1 AND l.loan_flg='Y' AND l.wf_type IN('O','HN','DC','YQ')  ");
		sql.append(" 		 AND l.payment_time <(SELECT CASE WHEN a.payment_time IS NULL THEN now() ELSE a.payment_time END FROM o2o_loanapply a WHERE a.loanapply_no=? )  ");
		sql.append("  		 AND l.loan_id = ?  ");
		sql.append("  		 ORDER BY l.payment_time DESC   ");
		sql.append("  		 LIMIT 0,1  ");//#LIMIT 19,10表示从第20条开始取10条，也就是20～30或者LIMIT 1查第一条数据 
		sql.append("     ) AS A ");
		sql.append(" ) ");
//		sqlPageQuery.addParam(FileTypeConstants.ENTERPRISE_ZX_IDCARD);//企业征信报告
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.addParam(loanId);
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}

	/**
	 * CXW loan-13093 信贷经理提交申报流程前，可以添加上最新征信报告 
	 * @param loanapplyNo
	 * @return
	 */
	public PageResult<Object[]> queryFqZxbgNew(String loanapplyNo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append("select u.id,uzx.document_no,zx.bill_code,uzx.user_full_name ,zx.zx_bank,uzx.zx_date,uzx.zx_td_date");  
		sql.append(",u.relation_date,u.relation_name,zx.zx_status from  o2o_zx_supplement u left join ");
		sql.append("o2o_zx zx ON  u.zx_no=zx.id and zx.is_del='N' left join o2o_loan_user_zx  uzx ");
		sql.append("on uzx.zx_no=zx.id  where u.is_delete='N' and u.loanapply_no=? ");
		sqlPageQuery.addParam(loanapplyNo);
		sqlPageQuery.setQuerySql(sql.toString());
		return this.findDataWithPage(sqlPageQuery);
	}
	
}
